package com.neuedu.jdbc.demo1;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.SimpleFormatter;

/***
 * 项目名称:  MyJDBC 
 * 包：  com.neuedu.jdbc.demo1 
 * date:   2025/11/7   10:26  
 * 描述:  
 *  @author : 张金山
 *  作者博客:   https://blog.fulfill.com.cn
 */
public class UserDemo {


    public void selectByID() {


        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");


            // 2 创建连接
            conn = DriverManager.getConnection(url, user, password);

            // 3 创建语句块stmt
            stmt = conn.createStatement();

            // 4 执行sql 语句
            String sql = "select  `id`,  `username`, `password`,  `nickname`,  `last_login`,  `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user where id = 1   ";
            rs = stmt.executeQuery(sql);

            /**
             * rs   getXXXX(columnName)
             * rs   getXXXX(columnIndex)
             */


            // 解析结果集
            if (rs.next()) {

                int index = 0;
                long id = rs.getLong(++index);
                String username = rs.getString(++index);
                String pd = rs.getString(++index);
                String nickname = rs.getString(++index);
                Date lastLogin = rs.getTimestamp(++index);
                String status = rs.getString(++index);
                String createtime = rs.getString(++index);
                Long createuser = rs.getLong(++index);
                Date updateTime = rs.getTimestamp(++index);
                Long updateuser = rs.getLong(++index);

                System.out.println(String.format(
                        "id = %s\tusername = %s\tpd = %s\tnickname = %s\tlastLogin = %s\tstatus = %s\tcreatetime = %s\tcreateuser = %s\tupdateTime = %s\tupdateuser = %s\r\n",
                        id, username, pd, nickname, lastLogin, status, createtime, createuser, updateTime, updateuser
                ));

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }


            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }


    }

    public void select() {


        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");


            // 2 创建连接
            conn = DriverManager.getConnection(url, user, password);

            // 3 创建语句块stmt
            stmt = conn.createStatement();

            // 4 执行sql 语句
            String sql = "select  `id`,  `username`, `password`,  `nickname`,  `last_login`,  `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user   ";
            rs = stmt.executeQuery(sql);

            /**
             * rs   getXXXX
             */


            // 解析结果集
            while (rs.next()) {
                long id = rs.getLong("id");
                String username = rs.getString("username");
                String pd = rs.getString("password");
                String nickname = rs.getString("nickname");
                Date lastLogin = rs.getTimestamp("last_login");
                String status = rs.getString("status");
                Date createTime = rs.getTimestamp("create_time");
                Long createuser = rs.getLong("create_user");
                Date updateTime = rs.getTimestamp("update_time");
                Long updateuser = rs.getLong("update_user");

                String strCreatetime = new SimpleDateFormat("yyyy年MM月dd日  HH点mm分ss秒").format(createTime);


                System.out.println(String.format(
                        "id = %s\tusername = %s\tpd = %s\tnickname = %s\tlastLogin = %s\tstatus = %s\tcreatetime = %s\tcreateuser = %s\tupdateTime = %s\tupdateuser = %s\r\n",
                        id, username, pd, nickname, lastLogin, status, strCreatetime, createuser, updateTime, updateuser
                ));

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }


            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }


    }


    public void update() {
        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        try {
            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);

            stmt = connection.createStatement();

            String sql = """
                        UPDATE
                          `user`
                        SET
                          `last_login` = '2025-11-07 10:34:08',
                          `status` = '1',
                          `update_time` = '2025-11-07 10:34:08',
                          `update_user` = 1
                        WHERE `id` = 3
                    
                    """;

            int count = stmt.executeUpdate(sql);
            System.out.println("更新成功 count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }


    public void insert() {

        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        try {
            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);

            stmt = connection.createStatement();

            String sql = """
                      INSERT INTO `his`.`user` ( `username`, `password`,  `nickname` ) VALUES  ( 'jshand', '666666', '教师' ) ;
                    
                    """;

            int count = stmt.executeUpdate(sql);
            System.out.println("插入成功 count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }


    public void delete() {


        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        try {
            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);

            stmt = connection.createStatement();

            String sql = """
                     delete  from user where  username = 'jshand' 
                    
                    """;

            int count = stmt.executeUpdate(sql);
            System.out.println("删除成功 count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }


    public static void main(String[] args) {
        UserDemo userDemo = new UserDemo();

        // 查询
        userDemo.select();


        // 修改
        // userDemo.update();


        // 添加
        // userDemo.insert();


        // 删除
        // userDemo.delete();


        // userDemo.selectByID();


    }
}
